# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from datetime import datetime
from pymysql.converters import escape_string

class GalleryPipeline:
    def __init__(self):
        self.host = "192.168.0.113"
        self.port = 3306
        self.user='coname'
        self.password = 'xZS6p4LDcKCwn4Yb'
        self.db="co"
        self.charset="utf8"
        self.num=1

    def open_mysql(self):#连接数据库
        try:
            self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.password,db=self.db,charset=self.charset)
            self.cursor = self.conn.cursor()
            print("打开数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("打开数据库失败>>>",e)

    def close_mysql(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            conn.close()
            print("关闭数据库成功!")
        except Exception as e:
            print('关闭数据库失败>>>',e)

    def save_mysql(self,data):#插入数据成功
        tamp='{0:%y%m%d%H%M%S}'.format(datetime.now())
        conn,cursor = self.open_mysql()
        try:
            if not cursor.execute('select srname from co_art where srname = "{}"'.format(data['srname'])):
                cursor.execute('insert into co_art (srname,fileurl,keywordid,srtype,srimgs,contact,introduction) values("{}","{}","{}","{}","{}","{}","{}")'.format(data['srname'],data['fileurl'],int(tamp)+self.num,'gallery',data['srimgs'],escape_string(data['contact']),escape_string(data['introduction'])))
                conn.commit()
                self.num=self.num+1
                print('插入数据成功')
        except Exception as e:
            print("插入数据失败>>>'{}'".format(data['srname']),e)
        self.close_mysql(conn,cursor)

    def process_item(self, item, spider):
        self.save_mysql(item)  
        return item
